package Control;
/**
 *
 * @author zhaoyingxi
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.AirLine;
import util.JdbcUtil;

public class AirLineControl {
	public static AirLine update(AirLine airLine)
	{
		Connection connection = JdbcUtil.getConnection();
		String sql = "update airlinetable set airLineNum=?,from2=?,to2=?,price=?,discountPrice=?,departureTime=?,max=?,discountNum=? where airLineNum=?";
		PreparedStatement statement = null;
		try {
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(sql);
				statement.setString(1, airLine.getAirLineNum());
				statement.setString(2, airLine.getFrom());
				statement.setString(3, airLine.getTo());
				statement.setDouble(4, airLine.getPrice());
				statement.setDouble(5, airLine.getDiscountPrice());
				statement.setString(6, airLine.getDepartureTime());
				statement.setInt(7, airLine.getMax());
				statement.setInt(8, airLine.getDiscountNum());
				statement.setString(9, airLine.getAirLineNum());
				statement.executeUpdate();
				connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				//Auto-generated catch block
				e1.printStackTrace();
				return null;
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return airLine;
	}
	
	public static AirLine update2(AirLine airLine)
	{
		Connection connection = JdbcUtil.getConnection();
		String sql = "update airlinetable set sumMoney=?,soldNum=? where airLineNum=?";
		PreparedStatement statement = null;
		try {
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(sql);
				statement.setString(1, airLine.getSumMoney()+"");
				statement.setString(2, airLine.getSoldNum()+"");
				statement.setString(3, airLine.getAirLineNum());
				statement.executeUpdate();
				connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				//Auto-generated catch block
				e1.printStackTrace();
				return null;
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return airLine;
	}
	public static boolean insert(AirLine airLine){
		boolean result = false;
		Connection connection = JdbcUtil.getConnection();
		String sql = "INSERT INTO airlinetable(airLineNum,from2,to2,price,discountPrice,departureTime,max,discountNum) values (?,?,?,?,?,?,?,?) ";
		PreparedStatement statement = null;
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(sql);
			statement.setString(1, airLine.getAirLineNum());
			statement.setString(2, airLine.getFrom());
			statement.setString(3, airLine.getTo());
			statement.setDouble(4, airLine.getPrice());
			statement.setDouble(5, airLine.getDiscountPrice());
			statement.setString(6, airLine.getDepartureTime());
			statement.setInt(7, airLine.getMax());
			statement.setInt(8, airLine.getDiscountNum());
			statement.executeUpdate();
			connection.commit();
			result=true;
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				//Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return result;
	}
	public static java.util.List<AirLine> find(){
		Connection connection = JdbcUtil.getConnection();
		java.util.List<AirLine> list = new ArrayList<AirLine>();
		String sql = "select * from airlinetable where 1=1 order by id desc";
		PreparedStatement statement = null;
		try {
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(sql);
				ResultSet resultSet = statement.executeQuery();
				while(resultSet.next()) {
					AirLine airline = new AirLine();
					airline.setAirLineNum(resultSet.getString("airLineNum"));
					airline.setDepartureTime(resultSet.getString("departureTime"));
					airline.setDiscountNum(resultSet.getInt("discountNum"));
					airline.setDiscountPrice(resultSet.getDouble("discountPrice"));
					airline.setFrom(resultSet.getString("from2"));
					airline.setId(resultSet.getInt("id"));
					airline.setMax(resultSet.getInt("max"));
					airline.setPrice(resultSet.getDouble("price"));
					airline.setSoldNum(resultSet.getInt("soldNum"));
					airline.setSumMoney(resultSet.getDouble("sumMoney"));
					airline.setTo(resultSet.getString("to2"));
					list.add(airline);
				}
				connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				//Auto-generated catch block
				e1.printStackTrace();
				return null;
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return list;
	}
	public static AirLine findByNum(String num)
	{
		AirLine airLine = new AirLine();
		Connection connection = JdbcUtil.getConnection();
		String sql = "SELECT * FROM airlinetable WHERE airLineNum=?";
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, num);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				airLine.setAirLineNum(resultSet.getString("airLineNum"));
				airLine.setDepartureTime(resultSet.getString("departureTime"));
				airLine.setDiscountNum(resultSet.getInt("discountNum"));
				airLine.setDiscountPrice(resultSet.getDouble("discountPrice"));
				airLine.setFrom(resultSet.getString("from2"));
				airLine.setId(resultSet.getInt("id"));
				airLine.setMax(resultSet.getInt("max"));
				airLine.setPrice(resultSet.getDouble("price"));
				airLine.setSoldNum(resultSet.getInt("soldNum"));
				airLine.setSumMoney(resultSet.getDouble("sumMoney"));
				airLine.setTo(resultSet.getString("to2"));
			}
		} catch (SQLException e) {
			//Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return airLine;
	}
	public static void deleteAirLine(String airLineNum){
		Connection connection = JdbcUtil.getConnection();
		String sql = "delete FROM airlinetable where airLineNum=?";
		PreparedStatement statement = null;
		try {
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(sql);
				statement.setString(1, airLineNum);
				statement.executeUpdate();
				connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				//Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
	}
}
